import json
from datetime import datetime
import time

import pandas as pd
from db_conn import pd_conn_zfw, pd_conn_wfw, get_redis


# 门店健康度明细导出
def healthDetailExport(path, startTime, endTime):
    def basicInfo():
        sql = """
            select 
                f_id '站点ID',f_name '站点名称',f_work_start_time '开业时间',f_province '省',f_city '市',
                f_district '区',f_business_category '渠道分类',f_station_num '机器代数'
            ,case 
                when f_station_num <= 6 then '愿景' when f_station_num = 10 then '阿尔法' 
                when f_station_num = 6004 then '愿景LITE 轮刷版' when f_station_num = 6001 then '愿景6SE1' 
                when f_station_num = 6002 then '愿景6SE2' when f_station_num = 6003 then '愿景LITE 高压水版' 
                when f_station_num = 10001 then '杭州太仆洗车机' when f_station_num = 10002 then '麦迪斯洗车机' 
                when f_station_num = 10003 then '安露洗车机' when f_station_num = 8001 then '隧道机' end '机器版本'
            ,case f_skill_score 
                when 1 then '自营' when 2 then '油站代理' when 3 then '社区代理' 
                when 4 then '买断' when 5 then '收益平分' end '分成类型'
            from t_factory where f_status = 0 and f_boxUid is not null 
        """
        return pd_conn_zfw(sql).set_index('站点ID')

    df_basic = basicInfo()

    def foamWax():
        sql = """
            SELECT f.f_id '站点ID',f_foam_water '泡沫液位值',f_wax_water '蜡水液位值', fp.f_update_time '更新时间'
            FROM t_factory_parameter fp
            join t_factory f on fp.f_factory_id = f.f_id
            where f.f_status = 0
        """
        df = pd_conn_zfw(sql).set_index('站点ID')
        df_m = df_basic.merge(df, how='left', on='站点ID')
        return df_m

    def cameraRecognitionRate():
        sql = """
            select 
                s.f_factory_id '站点ID'
                , GROUP_CONCAT(if(f_camera_type='A',if(s.f_camera2_index=1,'在线','不在线'),NULL)) '等候区在线？'
                , GROUP_CONCAT(if(f_camera_type='T',if(s.f_camera2_index=1,'在线','不在线'),NULL)) '车顶在线？'
                , GROUP_CONCAT(if(f_camera_type='B',if(s.f_camera2_index=1,'在线','不在线'),NULL)) '左车尾在线？'
                , o.`车顶识别`,o.`车顶识别`,o.`图片获取总数`,o.`订单总数`,o.`车顶识别率`,o.`车尾识别率`,o.`总单识别率`
            from 
                t_factory_station s
                left join (
                    SELECT
                        o.f_factory_id
                        , count(if(t1.f_type=1, t1.f_auto_no, null)) '车顶识别'
                        , count(if(t1.f_type=0, t1.f_auto_no, null)) '车尾识别'
                        , count(t1.f_order_id) '图片获取总数'
                        , count(o.f_order_id) '订单总数'
                        , count(if(t1.f_type=1, t1.f_auto_no, null))/count(o.f_order_id) '车顶识别率'
                        , count(if(t1.f_type=0, t1.f_auto_no, null))/count(o.f_order_id) '车尾识别率'
                        , count(t1.f_auto_no)/count(o.f_order_id) '总单识别率'
                    FROM
                        t_order o
                        left join t_order_car_record t1 on t1.f_order_id=o.f_order_id
                    WHERE 
                        o.f_order_date >= '%s' and o.f_order_date < '%s'
                    GROUP BY 
                        o.f_factory_id
                ) o on o.f_factory_id=s.f_factory_id
            GROUP BY s.f_factory_id
        """ % (startTime, endTime)
        df = pd_conn_zfw(sql).set_index('站点ID')
        df_m = df_basic.merge(df, how='left', on='站点ID')
        return df_m

    def customerComment():
        sql = """
            SELECT t2.f_id '站点ID',
            count(1) '总评数',
            count(if(t1.f_sa_score2 >3,1,null)) '4星及以上-清洁',
            count(if(t1.f_sa_score3 >3,1,null)) '4星及以上-环境',
            count(if(t1.f_sa_score4 >3,1,null)) '4星及以上-设备'
            from t_order_comment t1
            LEFT JOIN t_factory t2 on t1.f_factory_id = t2.f_id
            where t1.f_indate >= '%s' and t1.f_indate < '%s' and t1.f_status = 0 and t2.f_status = 0
            group by t2.f_id
        """ % (startTime, endTime)
        df = pd_conn_zfw(sql).set_index('站点ID')
        df_m = df_basic.merge(df, how='left', on='站点ID')
        return df_m

    def businessComment():
        # B端评价-微服务评价数据
        sql1 = """
            select f_factory_id '站点ID',f_mobile '用户手机号',f_answer_username '用户名称'
            ,ac.f_title '问卷标题', f_content '评价星级',a.f_create_time '评价时间'
            from  t_question_answer a 
            left join t_question_answer_content ac on ac.f_question_answer_id = a.f_id
            where a.f_question_id = 1 and ac.f_type = 3
            and a.f_create_time >= '%s' and a.f_create_time < '%s'
        """ % (startTime, endTime)
        df1 = pd_conn_wfw(sql1, 'ygl_task')
        df1 = df1.set_index('站点ID')

        # 月维护次数
        sql2 = f"""
            select f_factory_id '站点ID', count(1) '维护次数'
            from t_factory_business
            where
                f_startTime < '{endTime}' and ( f_endTime >= '{startTime}' or f_endTime is null )
                and f_work_status in (1,8)
                and TIMESTAMPDIFF(SECOND, f_startTime, ifnull(f_endTime, '{endTime}'))/60/60 >= 2
            group by f_factory_id
        """
        df2 = pd_conn_zfw(sql2).set_index('站点ID')
        df = df1.merge(df2, how='left', on='站点ID')
        df_m = df_basic.merge(df, how='left', on='站点ID')
        return df_m

    def cleaningRate():
        sql = f"""
            select 
                o.f_factory_id '站点ID', o.f_order_id '订单ID', o.f_mobile '扫码手机', 
                a.f_alarm_name '报警信息', a.f_upd_time '报警时间'
            from (
                select f_factory_id,f_mobile,f_order_id
                from t_order where f_order_date >= '{startTime}' and f_order_date < '{endTime}' and f_status > 0
            ) o
            join t_washcar_alarm a on a.f_order_id = o.f_order_id 
                and f_alarm_name not in (
                    "右后竖防撞胶条触发","左后竖防撞胶条触发","右前竖防撞胶条触发","左前竖防撞胶条触发","龙门前进碰到防撞杆","右前下防撞胶条触发",
                    "右后上防撞胶条触发","左前下防撞胶条触发","左后上防撞胶条触发","左后下防撞胶条触发","柜门防撞胶条触发","龙门后退碰到防撞杆",
                    "行人防撞胶条触发","右后下防撞胶条触发","用户使用手机APP停止","洗车机上急停按钮按下",      -- 客户人为原因报警
                    "水箱缺水","水系统跳闸或膜堵塞","请增加MBR膜数量","泡沫水压低","通讯异常-顶刷旋转","轮毂检测异常，跳过轮毂",
                    "集水井溢水","气压低报警","水压过大","左刷高低位异常","变频器异常","光电A被遮挡","正常归位超时","光电B被遮挡",
                    "防冻排水已完成","风机轮子报错","风机状态掉线","洗车机雷达掉线","引导雷达状态掉线","顶刷旋转电流超高","污水高液位",
                    "工作区防追尾被遮挡","风机3&4变频器故障","洗车机背门未关好","洗车机轮子")        -- 不停机报警
        """

        df = pd_conn_zfw(sql).set_index('站点ID')
        df_m = df_basic.merge(df, how='left', on='站点ID')
        return df_m

    def monthInterruptTime():
        sql = f"""
            select 
                f_factory_id '站点ID', f_work_status_name '维护类型', f_companyTip '维护原因', 
                f_startTime '开始时间', ifnull(f_endTime, '{endTime}') '结束时间', 
                TIMESTAMPDIFF(SECOND, f_startTime, ifnull(f_endTime, '{endTime}'))/60/60 '维护时长h'
            from t_factory_business fb
                left join t_factory_business_work fbw on fbw.f_work_status_num = fb.f_work_status
            where
                f_startTime < '{endTime}' and ( f_endTime >= '{startTime}' or f_endTime is null )
                and f_work_status not in (5,11,12,13,15)
                and TIMESTAMPDIFF(SECOND, f_startTime, ifnull(f_endTime, '{endTime}'))/60/60 >= 2
        """
        df = pd_conn_zfw(sql).set_index('站点ID')
        df_m = df_basic.merge(df, how='left', on='站点ID')
        return df_m

    def monthRepairTime():
        sql = f"""
            select 
                f_factory_id '站点ID', f_work_status_name '维护类型', f_companyTip '维护原因', 
                f_startTime '开始时间', ifnull(f_endTime, '{endTime}') '结束时间', 
                TIMESTAMPDIFF(SECOND, f_startTime, ifnull(f_endTime, '{endTime}'))/60/60 '维护时长h'
            from t_factory_business fb
                left join t_factory_business_work fbw on fbw.f_work_status_num = fb.f_work_status
            where
                f_startTime < '{endTime}' and ( f_endTime >= '{startTime}' or f_endTime is null )
                and f_work_status in (1, 8)
                and TIMESTAMPDIFF(SECOND, f_startTime, ifnull(f_endTime, '{endTime}'))/60/60 >= 2
        """
        df = pd_conn_zfw(sql).set_index('站点ID')
        df_m = df_basic.merge(df, how='left', on='站点ID')
        return df_m

    def redis():
        title = ("功能按钮-防碰撞功能开启",
                 "防撞功能设置-左前竖防撞胶条功能", "防撞功能设置-右前竖防撞胶条功能", "防撞功能设置-左前下防撞胶条功能",
                 "防撞功能设置-右前下防撞胶条功能", "防撞功能设置-左后竖防撞胶条功能", "防撞功能设置-左后上防撞胶条功能",
                 "防撞功能设置-左后下防撞胶条功能", "防撞功能设置-右后竖防撞胶条功能", "防撞功能设置-右后上防撞胶条功能",
                 "防撞功能设置-右后下防撞胶条功能",  "防撞功能设置-行人防撞胶条功能", "防撞功能设置-柜门防撞胶条功能",
                 "功能按钮-轮毂屏蔽功能", "功能按钮-超高检测功能", "功能按钮-水压检测功能",
                 )
        title_sense = [
            "传感器检测-左前竖防撞胶条", "传感器检测-右前竖防撞胶条", "传感器检测-左前下防撞胶条", "传感器检测-右前下防撞胶条",
            "传感器检测-左后竖防撞胶条", "传感器检测-左后上防撞胶条", "传感器检测-左后下防撞胶条", "传感器检测-右后竖防撞胶条",
            "传感器检测-右后上防撞胶条", "传感器检测-右后下防撞胶条", "传感器检测-行人防撞胶条", "传感器检测-柜门防撞胶条",
            "传感器检测-左轮刷开位", "传感器检测-右轮刷开位", "传感器检测-超高"
        ]
        title_sense_ex = ["传感器检测-循环水压信号", "传感器检测-缺水保护"]

        touch_setting_points = [
            "防撞功能设置-左前下防撞胶条功能", "防撞功能设置-右前下防撞胶条功能",
            "防撞功能设置-左后下防撞胶条功能", "防撞功能设置-右后下防撞胶条功能",
            "防撞功能设置-左后上防撞胶条功能", "防撞功能设置-右后上防撞胶条功能",
            "防撞功能设置-左后竖防撞胶条功能", "防撞功能设置-右后竖防撞胶条功能",
            "防撞功能设置-左前竖防撞胶条功能", "防撞功能设置-右前竖防撞胶条功能",
            "防撞功能设置-行人防撞胶条功能", "防撞功能设置-柜门防撞胶条功能"
        ]

        touch_sense_points = [
            "传感器检测-左前下防撞胶条", "传感器检测-右前下防撞胶条",  # [:2]
            "传感器检测-左后下防撞胶条", "传感器检测-右后下防撞胶条",  # [2:4]
            "传感器检测-左后上防撞胶条", "传感器检测-右后上防撞胶条",  # [4:6]
            "传感器检测-左后竖防撞胶条", "传感器检测-右后竖防撞胶条",  # [6:8]
            "传感器检测-左前竖防撞胶条", "传感器检测-右前竖防撞胶条",  # [8:10]
            "传感器检测-行人防撞胶条", "传感器检测-柜门防撞胶条",  # [10:12]
        ]
        # 1-6 & 8001
        touch_set_1_6 = touch_setting_points[:10]
        touch_sense_1_6 = touch_sense_points[:10]

        # 6se
        touch_set_6se = touch_setting_points[6:]
        touch_sense_6se = touch_sense_points[6:]

        # 愿景7
        touch_set_7 = touch_setting_points[8:11]
        touch_sense_7 = touch_sense_points[8:11]

        # 全部防撞设置点位

        # 获取POINT中点位对应的value值
        def get_point_value(json_str):
            loads = json.loads(json_str)
            if "value" in loads:
                return loads['value']
            return None

        # 判断功能是否开启
        def check_item(point_redis_json, item):
            if point_redis_json:
                if item in point_redis_json:
                    value = get_point_value(point_redis_json[item])
                    if item == '功能按钮-轮毂屏蔽功能':
                        if str(value) == '1':
                            return '关闭'
                        elif str(value) == '0':
                            return '开启'
                        else:
                            return None
                    else:
                        if str(value) == '0':
                            return '关闭'
                        elif str(value) == '1':
                            return '开启'
                        else:
                            return None
                else:
                    return '无点位'
            return '无盒子'

        # 传感器值
        def sense_value(point_redis_json, item):
            if point_redis_json:
                if item in point_redis_json:
                    value = get_point_value(point_redis_json[item])
                    if value:
                        return value
                    else:
                        return None
                else:
                    return '无点位'
            else:
                return '无盒子'

        # 判断盒子是否在线
        def check_status(status_json_str):
            if '是否在线' in status_json_str:
                status = status_json_str['是否在线']
                if status == '0':
                    return '在线'
                elif status == '1':
                    return '不在线'
                else:
                    return None
            else:
                return '无点位'

        # 外部洗车机
        other_station = (10, 10001, 10002, 10003, 10004, 10005, 10006, 8002)
        station_num_600x = (6001, 6002, 6003, 6004)

        df = pd_conn_zfw(
            """select f_id '站点ID',f_boxUid, f_station_num from t_factory where f_status = 0 and f_boxUid is not null"""
        )
        df = df.set_index('站点ID')

        redis = get_redis()

        status_key = "MACHINE_STATUS_%s"
        point_key = "FBOX_POINT_%s"

        for f_id, f_boxUid, station_num in zip(df.index, df.f_boxUid, df.f_station_num):
            status_json_str = redis.hgetall(status_key % f_boxUid)
            point_json_str = redis.hgetall(point_key % f_boxUid)
            df.loc[f_id, '盒子在线'] = check_status(status_json_str)
            for item in title:
                df.loc[f_id, item] = check_item(point_json_str, item)
            if station_num in station_num_600x:
                for item in title_sense:
                    df.loc[f_id, item] = sense_value(point_json_str, item)
                df.loc[f_id, "循环水压信号/缺水保护"] = sense_value(point_json_str, title_sense_ex[1])
            else:
                for item in title_sense:
                    df.loc[f_id, item] = sense_value(point_json_str, item)
                df.loc[f_id, "循环水压信号/缺水保护"] = sense_value(point_json_str, title_sense_ex[0])

        for f_id, station_num in zip(df.index, df.f_station_num):
            if df.loc[f_id, touch_sense_points].str.contains('关闭').all() and df.loc[f_id, "功能按钮-防碰撞功能开启"] == '关闭':
                setting_online = "功能按钮-防碰撞功能开启" + '；'
            else:
                setting_online = ''
            setting_offline = ''

            def point_lose(settings, senses):
                pl = ''
                for sett, sens in zip(settings, senses):
                    if df.loc[f_id, sett] is None:
                        pl = pl + sett + '；'
                return pl

            if station_num in (6001, 6002):
                point_losing = point_lose(touch_set_6se, touch_sense_6se)
            elif station_num in (6003, 6004):
                point_losing = point_lose(touch_set_7, touch_sense_7)
            else:
                point_losing = point_lose(touch_set_1_6, touch_sense_1_6)

            for sense, setting in zip(touch_sense_points, touch_setting_points):
                if df.loc[f_id, setting] == '关闭':
                    if df.loc[f_id, sense] == '0':
                        setting_online = setting_online + setting + '；'
                    elif df.loc[f_id, sense] == '1':
                        setting_offline = setting_offline + setting + '；'
            if len(setting_online) > 0:
                setting_online = '线上手动开启：（' + setting_online + '）'
            if len(setting_offline) > 0:
                setting_offline = '线下恢复：（' + setting_offline + '）'

            df.loc[f_id, '防撞功能恢复'] = setting_online + setting_offline
            df.loc[f_id, '点位缺失'] = point_losing

        df = df.drop('f_boxUid', axis=1)
        df_m = df_basic.merge(df, how='left', on='站点ID')

        return df_m

    def to_excel():

        title_time1 = startTime[-5:-3] + startTime[-2:]
        title_time2 = endTime[-5:-3] + endTime[-2:]
        title_time3 = datetime.strftime(datetime.now(), '%H%M%S')

        print('门店健康度明细导出中...')
        writer = pd.ExcelWriter(r'{}\门店健康度明细{}~{}({}).xlsx'.format(path, title_time1, title_time2, title_time3))
        tm1 = time.time()
        foamWax().to_excel(writer, sheet_name='泡沫水水蜡')
        tm2 = time.time()
        print('泡沫水水蜡明细已生成！', round(tm2-tm1, 0), 's')
        cameraRecognitionRate().to_excel(writer, sheet_name='摄像头识别率')
        tm3 = time.time()
        print('摄像头识别率明细已生成！', round(tm3-tm2, 0), 's')
        customerComment().to_excel(writer, sheet_name='C端评价')
        tm4 = time.time()
        print('C端评价明细已生成！', round(tm4-tm3, 0), 's')
        businessComment().to_excel(writer, sheet_name='B端评价')
        tm5 = time.time()
        print('B端评价明细已生成！', round(tm5 - tm4, 0), 's')
        cleaningRate().to_excel(writer, sheet_name='清洗完成度（未完成订单）')
        tm6 = time.time()
        print('清洗完成度（未完成订单）明细已生成！', round(tm6-tm5, 0), 's')
        monthInterruptTime().to_excel(writer, sheet_name='月停机时长')
        tm7 = time.time()
        print('月停机时长明细已生成！', round(tm7-tm6, 0), 's')
        monthRepairTime().to_excel(writer, sheet_name='月维护时长')
        tm8 = time.time()
        print('月维护时长明细已生成！', round(tm8 - tm7, 0), 's')
        redis().to_excel(writer, sheet_name='防撞杆、清洗功能')
        tm9 = time.time()
        print('防撞杆、清洗功能明细已生成！', round(tm9-tm8, 0), 's')
        writer.save()
        writer.close()
        print('门店健康度明细已全部导出完毕，期待您的下次光临！')

    return to_excel()


if __name__ == '__main__':
    path = 'D:\Download\python丽莎'
    startTime = '2022-04-01'
    endTime = '2022-04-02'
    healthDetailExport(path, startTime, endTime)
